﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using Pantry.Commons;
using Pantry.Entity;

namespace Pantry.DataAccess
{
    public class TrustDistrictDao:PantryDao
    {
        public TrustDistrictDao()
        {
        }

        public TrustDistrictDao(SqlConnection sqlConnection): base(sqlConnection)
        {
        }
        
        public DataSet SelectAll()
        {
            return SqlHelper.ExecuteDataSet(SqlConnect, CommandType.Text, DBSchemas.SqlSelectAll);
        }

        public override DataTable RetrieveData(string conditionField, string conditionValue, SqlDbType sqlType)
        {
            var sqlCommand = String.Format(CultureInfo.CurrentCulture, DBSchemas.SqlRetrieveTrustDistrictsByRegion,
                                           conditionField);
            var parameters = new SqlParameter("@ConditionField", sqlType) { Value = conditionValue };
            return RetrieveData(sqlCommand, parameters);
        }

        public override object RetrieveEntity(int id)
        {
            if (id < 1)
            {
                return null;
            }

            var parameter = new SqlParameter("@TrustDistrictId", SqlDbType.Int) { Value = id };
            var dataTable = RetrieveData(DBSchemas.SqlRetrieveTrustDistrict, parameter);
            if ((dataTable == null)||(dataTable.Rows.Count<1))
            {
                return null;
            }

            var dataRow = dataTable.Rows[0];
            var trustDistrict = new TrustDistrict
            {
                TrustDistrictId = Convert.ToInt32(dataRow["TrustDistrictId"], CultureInfo.CurrentCulture),
                TrustDistrictName = dataRow["TrustDistrictName"].ToString(),
                Description = dataRow["Description"].ToString(),
                TrustRegionId = Convert.ToInt32(dataRow["TrustRegionId"], CultureInfo.CurrentCulture),
                TrustRegionName = dataRow["TrustRegionName"].ToString(),
                IsActive = Convert.ToBoolean(dataRow["IsActive"], CultureInfo.CurrentCulture)
            };
            return trustDistrict;
        }

        public override int Insert(object sender)
        {
            var trustDistrict = (TrustDistrict)sender;
            if (trustDistrict == null)
            {
                return 0;
            }

            var result = CheckUnique(@"TrustDistrict", @"TrustDistrictName", trustDistrict.TrustDistrictName);
            if (result > 0)
            {
                return -2;
            }

            var parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@TrustDistrictName", SqlDbType.VarChar, 100)
                                {Value = trustDistrict.TrustDistrictName};
            parameters[1] = new SqlParameter("@Description", SqlDbType.VarChar, 400)
                                {Value = trustDistrict.Description};
            parameters[2] = new SqlParameter("@TrustRegionId", SqlDbType.Int) {Value = trustDistrict.TrustRegionId};
            return ExecuteNonQuery(DBSchemas.SqlInsertTrustDistrict, parameters);
        }

        public override int Update(object sender)
        {
            var trustDistrict = (TrustDistrict)sender;
            if (trustDistrict == null)
            {
                return 0;
            }

            var result = CheckUniqueForAmend(@"TrustDistrict", @"TrustDistrictName", trustDistrict.TrustDistrictName, @"TrustDistrictId", trustDistrict.TrustDistrictId);
            if (result != 0)
            {
                return -2;
            }

            var parameters = new SqlParameter[4];
            parameters[0] = new SqlParameter("@TrustDistrictName", SqlDbType.VarChar, 100) { Value = trustDistrict.TrustDistrictName };
            parameters[1] = new SqlParameter("@Description", SqlDbType.VarChar, 400) { Value = trustDistrict.Description };
            parameters[2] = new SqlParameter("@TrustRegionId", SqlDbType.Int) { Value = trustDistrict.TrustRegionId };
            parameters[3] = new SqlParameter("@TrustDistrictId", SqlDbType.Int) { Value = trustDistrict.TrustDistrictId };
            return ExecuteNonQuery(DBSchemas.SqlUpdateTrustDistrict, parameters);
        }
    }
}